import * as XLSX from 'xlsx';

// 模拟字典数据
const sys_user_sex = [
  { value: 0, label: '男' },
  { value: 1, label: '女' }
];

const id_type = [
  { value: '身份证', label: '身份证' },
  { value: '护照', label: '护照' }
];

const sys_enable = [
  { value: 1, label: '启用' },
  { value: 2, label: '停用' }
];
const transport_section = [
  { value: 1, label: '国内' },
  { value: 2, label: '国外' }
];

const bas_currency = [
  { value: 4, label: '人民币' },
  { value: 5, label: '美元' },
  { value: 6, label: '欧元' },
 
];

// 格式化金额（保留两位小数）
function formatAmount(amount: any): string {
  const num = parseFloat(amount);
  if (isNaN(num)) {
    return '0.00'; 
  }
  return num.toFixed(2); 
}
// 格式化汇率（保留六位小数）
function formatRate(rate: any): string {
  const num = parseFloat(rate);
  if (isNaN(num)) {
    return '0.000000';
  }
  return num.toFixed(6);
}


// 格式化日期（yyyy-mm-dd）
function parseTime(date: string | number | Date, fmt: string): string {
  const dateObj = new Date(date);
  const o: { [key: string]: string } = {
    '{y}': dateObj.getFullYear().toString(),
    '{m}': (dateObj.getMonth() + 1).toString().padStart(2, '0'),
    '{d}': dateObj.getDate().toString().padStart(2, '0'),
  };
  return fmt.replace(/{(y|m|d)+}/g, (match) => o[match]);
}

// 根据字典值获取标签
function getLabelFromDict(dict: any[], value: string | number): string | number {
  const item = dict.find((item) => item.value === value);
  return item ? item.label : value; // 如果找不到，则返回原值
}

//员工列表
export function exportStaffToExcel(staffList: any[], filename: string = '员工列表.xlsx') {
  // 定义表头
  const header = [
    '员工编码',
    '员工名称',
    '所属部门',
    '性别',
    '证件类型',
    '证件号码',
    '家庭地址',
    '手机号',
    '备注',
    '启用状态'
  ];

  // 格式化数据
  const data = staffList.map((staff) => [
    staff.code,
    staff.name,
    staff.deptName,
    getLabelFromDict(sys_user_sex, staff.sex),  // 性别映射
    getLabelFromDict(id_type, staff.idType),    // 证件类型映射
    staff.idNumber,
    staff.address,
    staff.phone,
    staff.remark,
    getLabelFromDict(sys_enable, staff.staffStatus), // 启用状态映射
  ]);

  // 创建工作表
  const ws = XLSX.utils.aoa_to_sheet([header, ...data]);

  // 创建工作簿
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '员工列表');

  // 导出 Excel
  XLSX.writeFile(wb, filename);
}
//销售合同列表
export function exportSaleContractToExcel(saleContractList: any[], filename: string = '销售合同列表.xlsx') {
  // 定义表头
  const header = [
    '销售合同号',
    '项目名称',
    '运输路段',
    '客户名称',
    '签订日期',
    '签订部门',
    '签订人员',
    '原币价税合计',
    '原币无税金额',
    '原币税额',
    '币种',
    '汇率',
    '本币价税合计',
    '本币无税金额',
    '本币税额',
    '转关税',
    '保险费',
    '押车费',
    '备注'
  ];

  // 格式化数据
  const data = saleContractList.map((contract) => [
    contract.saleContractNum,
    contract.projectName,
    getLabelFromDict(transport_section, contract.transportSection),  // 运输路段映射
    contract.customerName,
    parseTime(contract.signDate, '{y}-{m}-{d}'),  // 签订日期格式化
    contract.signDeptName,
    contract.signStaffName,
    formatAmount(contract.originalTotalAmount),  // 原币价税合计格式化
    formatAmount(contract.originalAmount),  // 原币无税金额格式化
    formatAmount(contract.originalTaxedAmount),  // 原币税额格式化
    getLabelFromDict(bas_currency, contract.currency), // 币种映射
    formatRate(contract.exchangeRate),  // 汇率格式化
    formatAmount(contract.localTotalAmount),  // 本币价税合计格式化
    formatAmount(contract.localAmount),  // 本币无税金额格式化
    formatAmount(contract.localTaxedAmount),  // 本币税额格式化
    formatAmount(contract.transferTaxedAmount),  // 转关税格式化
    formatAmount(contract.insuranceAmount),  // 保险费格式化
    formatAmount(contract.escortAmount),  // 押车费格式化
    contract.remark
  ]);

  // 创建工作表
  const ws = XLSX.utils.aoa_to_sheet([header, ...data]);

  // 创建工作簿
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '销售合同列表');

  // 导出 Excel
  XLSX.writeFile(wb, filename);
}
//采购合同列表
export function exportPurchaseContractToExcel(purchaseContractList: any[], filename: string = '采购合同列表.xlsx') {
  // 定义表头
  const header = [
    '关联销售合同号',
    '采购合同号',
    '项目名称',
    '运输路段',
    '供应商',
    '签订日期',
    '签订部门',
    '签订员工',
    '原币价税合计',
    '原币无税金额',
    '原币税额',
    '币种',
    '汇率',
    '本币价税合计',
    '本币无税金额',
    '本币税额',
    '转关税',
    '保险费',
    '押车费',
    '备注'
  ];

  // 格式化数据
  const data = purchaseContractList.map((contract) => [
    contract.saleContractNum,
    contract.purchaseContractNum,
    contract.projectName,
    getLabelFromDict(transport_section, contract.transportSection),  // 运输路段映射
    contract.supplierName,
    parseTime(contract.signDate, '{y}-{m}-{d}'),  // 签订日期格式化
    contract.signDeptName,
    contract.signStaffName,
    formatAmount(contract.originalTotalAmount),  // 原币价税合计格式化
    formatAmount(contract.originalAmount),  // 原币无税金额格式化
    formatAmount(contract.originalTaxedAmount),  // 原币税额格式化
    getLabelFromDict(bas_currency, contract.currency), // 币种映射
    formatRate(contract.exchangeRate),  // 汇率格式化
    formatAmount(contract.localTotalAmount),  // 本币价税合计格式化
    formatAmount(contract.localAmount),  // 本币无税金额格式化
    formatAmount(contract.localTaxedAmount),  // 本币税额格式化
    formatAmount(contract.transferTaxedAmount),  // 转关税格式化
    formatAmount(contract.insuranceAmount),  // 保险费格式化
    formatAmount(contract.escortAmount),  // 押车费格式化
    contract.remark
  ]);

  // 创建工作表
  const ws = XLSX.utils.aoa_to_sheet([header, ...data]);

  // 创建工作簿
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '采购合同列表');

  // 导出 Excel
  XLSX.writeFile(wb, filename);
}
//销售发票号
export function exportSaleInvoiceExcel(list: any[], filename = '销售发票列表.xlsx') {
  const rows: any[] = [];

  list.forEach(main => {
    const childList = main.saleInvoiceList || [];
    if (childList.length > 0) {
      childList.forEach(child => {
        rows.push({
          saleContractNum: main.saleContractNum,
          projectName: main.projectName,
          transportSection: getLabelFromDict(transport_section, main.transportSection),
          customerName: main.customerName,
          currencyMain: getLabelFromDict(bas_currency, main.currency),
          localTotalAmountMain: formatAmount(main.localTotalAmount),
          remarkMain: main.remark,

          saleInvoiceNum: child.saleInvoiceNum,
          voucherNum: child.voucherNum,
          recordDate: child.recordDate ? parseTime(child.recordDate, '{y}-{m}-{d}') : '',
          originalTotalAmount: formatAmount(child.originalTotalAmount),
          originalAmount: formatAmount(child.originalAmount),
          originalTaxAmount: formatAmount(child.originalTaxAmount),
          currencySub: getLabelFromDict(bas_currency, child.currency),
          exchangeRate: formatRate(child.exchangeRate),
          localTotalAmount: formatAmount(child.localTotalAmount),
          localAmount: formatAmount(child.localAmount),
          localTaxAmount: formatAmount(child.localTaxAmount),
          remarkSub: child.remark
        });
      });
    } else {
      rows.push({
        saleContractNum: main.saleContractNum,
        projectName: main.projectName,
        transportSection: getLabelFromDict(transport_section, main.transportSection),
        customerName: main.customerName,
        currencyMain: getLabelFromDict(bas_currency, main.currency),
        localTotalAmountMain: formatAmount(main.localTotalAmount),
        remarkMain: main.remark
      });
    }
  });

  const headers = [
    '销售合同号', '项目名称', '运输路段', '客户名称',
    '合同币种', '本币价税合计(主)', '合同备注',
    '销售发票号', '相关凭证号', '发票入账日期',
    '原币价税合计', '原币无税金额', '原币税额',
    '发票币种', '汇率',
    '本币价税合计', '本币无税金额', '本币税额',
    '发票备注'
  ];

  const worksheetData = [
    headers,
    ...rows.map(row => [
      row.saleContractNum,
      row.projectName,
      row.transportSection,
      row.customerName,
      row.currencyMain,
      row.localTotalAmountMain,
      row.remarkMain,
      row.saleInvoiceNum,
      row.voucherNum,
      row.recordDate,
      row.originalTotalAmount,
      row.originalAmount,
      row.originalTaxAmount,
      row.currencySub,
      row.exchangeRate,
      row.localTotalAmount,
      row.localAmount,
      row.localTaxAmount,
      row.remarkSub
    ])
  ];

  const ws = XLSX.utils.aoa_to_sheet(worksheetData);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '销售发票列表');

  XLSX.writeFile(wb, filename);
}
//采购发票
export function exportPurchaseInvoiceExcel(list: any[], filename = '采购发票列表.xlsx') {
  const rows: any[] = [];

  list.forEach(main => {
    const childList = main.purchaseInvoiceList || [];
    if (childList.length > 0) {
      childList.forEach(child => {
        rows.push({
          purchaseContractNum: main.purchaseContractNum,
          projectName: main.projectName,
          transportSection: getLabelFromDict(transport_section, main.transportSection),
          supplierName: main.supplierName,
          currencyMain: getLabelFromDict(bas_currency, main.currency),
          localTotalAmountMain: formatAmount(main.localTotalAmount),
          remarkMain: main.remark,

          purchaseInvoiceNum: child.purchaseInvoiceNum,
          voucherNum: child.voucherNum,
          recordDate: child.recordDate ? parseTime(child.recordDate, '{y}-{m}-{d}') : '',
          originalTotalAmount: formatAmount(child.originalTotalAmount),
          originalAmount: formatAmount(child.originalAmount),
          originalTaxAmount: formatAmount(child.originalTaxAmount),
          currencySub: getLabelFromDict(bas_currency, child.currency),
          exchangeRate: formatRate(child.exchangeRate),
          localTotalAmount: formatAmount(child.localTotalAmount),
          localAmount: formatAmount(child.localAmount),
          localTaxAmount: formatAmount(child.localTaxAmount),
          remarkSub: child.remark
        });
      });
    } else {
      rows.push({
        purchaseContractNum: main.purchaseContractNum,
        projectName: main.projectName,
        transportSection: getLabelFromDict(transport_section, main.transportSection),
        supplierName: main.supplierName,
        currencyMain: getLabelFromDict(bas_currency, main.currency),
        localTotalAmountMain: formatAmount(main.localTotalAmount),
        remarkMain: main.remark
      });
    }
  });

  const headers = [
    '采购合同号', '项目名称', '运输路段', '供应商名称',
    '合同币种', '本币价税合计(主)', '合同备注',
    '采购发票号', '相关凭证号', '发票入账日期',
    '原币价税合计', '原币无税金额', '原币税额',
    '发票币种', '汇率',
    '本币价税合计', '本币无税金额', '本币税额',
    '发票备注'
  ];

  const worksheetData = [
    headers,
    ...rows.map(row => [
      row.purchaseContractNum,
      row.projectName,
      row.transportSection,
      row.supplierName,
      row.currencyMain,
      row.localTotalAmountMain,
      row.remarkMain,
      row.purchaseInvoiceNum,
      row.voucherNum,
      row.recordDate,
      row.originalTotalAmount,
      row.originalAmount,
      row.originalTaxAmount,
      row.currencySub,
      row.exchangeRate,
      row.localTotalAmount,
      row.localAmount,
      row.localTaxAmount,
      row.remarkSub
    ])
  ];

  const ws = XLSX.utils.aoa_to_sheet(worksheetData);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '采购发票列表');

  XLSX.writeFile(wb, filename);
}
//收款列表
export function exportReceiveBillExcel(billList: any[], filename = '收款列表.xlsx') {
  // 定义表头
  const header = [
    '销售合同号',
    '项目名称',
    '客户名称',
    '运输路段',
    '币种',
    '本币价税合计',
    '收款金额',
    '入账日期',
    '相关凭证号',
    '备注'
  ];
  // 组装数据
  const data = billList.map(item => ([
    item.saleContractNum,
    item.projectName,
    item.customerName,
    getLabelFromDict(transport_section, item.transportSection),
    getLabelFromDict(bas_currency, item.currency),
    formatAmount(item.localTotalAmount),
    formatAmount(item.billAmount),
    item.recordDate ? parseTime(item.recordDate, '{y}-{m}-{d}') : '',
    item.voucherNum,
    item.remark || ''
  ]));

  // 创建工作表
  const ws = XLSX.utils.aoa_to_sheet([header, ...data]);

  // 创建工作簿并写入
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '收款列表');
  XLSX.writeFile(wb, filename);
}

//付款列表
export function exportPaymentBillExcel(billList: any[], filename = '付款列表.xlsx') {
  // 定义表头
  const header = [
    '采购合同号',
    '项目名称',
    '供应商名称',
    '运输路段',
    '币种',
    '本币价税合计',
    '付款金额',
    '入账日期',
    '相关凭证号',
    '备注'
  ];

  // 组装数据
  const data = billList.map(item => ([
    item.purchaseContractNum,
    item.projectName,
    item.supplierName,
    getLabelFromDict(transport_section, item.transportSection),
    getLabelFromDict(bas_currency, item.currency),
    formatAmount(item.localTotalAmount),
    formatAmount(item.billAmount),
    item.recordDate ? parseTime(item.recordDate, '{y}-{m}-{d}') : '',
    item.voucherNum,
    item.remark || ''
  ]));

  // 创建工作表
  const ws = XLSX.utils.aoa_to_sheet([header, ...data]);

  // 创建工作簿并写入
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '付款列表');
  XLSX.writeFile(wb, filename);
}


//报表
export function exportReportExcel(flatRows: any[], filename = '报表.xlsx') {
  if (!flatRows || flatRows.length === 0) return;

  // 1️⃣ 表头二维数组（两行多级表头）
  const header1 = [
    '销售合同号', '项目名称', '币种', '运输路段', '客户名称',
    '本币价税合计', '本币无税金额', '本币税额',
    '销售发票明细', '', '',
    '收款明细', '', '',
    '采购合同', '', '', '', '', '',
    '采购发票明细', '', '',
    '付款明细', '', ''
  ];
  const header2 = [
    '', '', '', '', '', '', '', '',
    '发票入账日期', '本币价税合计', '凭证号',
    '收款入账日期', '收款金额', '相关凭证号',
    '采购合同号', '供应商名称', '币种', '本币价税合计', '本币无税金额', '本币税额',
    '发票入账日期', '本币价税合计', '相关凭证号',
    '付款入账日期', '金额', '相关凭证号'
  ];

  const aoa: any[][] = [header1, header2];

  // 2️⃣ 填充数据
  flatRows.forEach(row => {
    aoa.push([
      row.saleContractNum,
      row.projectName,
      getLabelFromDict(bas_currency, row.saleCurrency),
      getLabelFromDict(transport_section, row.transportSection),
      row.customerName,
      row.contractLocalTotalAmount,
      row.contractLocalAmount,
      row.contractLocalTaxedAmount,

      row.saleInvoiceDate ? parseTime(row.saleInvoiceDate, '{y}-{m}-{d}') : '',
      row.saleInvoiceAmount,
      row.saleInvoiceVoucher,

      row.receiveDate ? parseTime(row.receiveDate, '{y}-{m}-{d}') : '',
      row.receiveAmount,
      row.receiveVoucher,

      row.purchaseContractNum,
      row.supplierName,
      getLabelFromDict(bas_currency, row.purchaseCurrency),
      row.purchaseLocalTotalAmount,
      row.purchaseLocalAmount,
      row.purchaseLocalTaxedAmount,

      row.purchaseInvoiceDate ? parseTime(row.purchaseInvoiceDate, '{y}-{m}-{d}') : '',
      row.purchaseInvoiceAmount,
      row.purchaseInvoiceVoucher,

      row.paymentDate ? parseTime(row.paymentDate, '{y}-{m}-{d}') : '',
      row.paymentAmount,
      row.paymentVoucher
    ]);
  });

  // 3️⃣ 创建工作表
  const ws = XLSX.utils.aoa_to_sheet(aoa);

  // 4️⃣ 自动计算合并区域
  const merges: XLSX.Range[] = [];
  const mainCols = ['saleContractNum','projectName','customerName','transportSection','saleCurrency','contractLocalTotalAmount','contractLocalAmount','contractLocalTaxedAmount'];
  const purchaseCols = ['purchaseContractNum','supplierName','purchaseCurrency','purchaseLocalTotalAmount','purchaseLocalAmount','purchaseLocalTaxedAmount'];

  // 销售合同整组合并
  let r = 2; // 数据从第三行开始
  const contractMap = new Map<string, { start: number, count: number }>();
  flatRows.forEach((row, idx) => {
    const key = row.saleContractId;
    if (!contractMap.has(key)) contractMap.set(key, { start: r + idx, count: 0 });
    contractMap.get(key)!.count += 1;
  });

  contractMap.forEach(({ start, count }) => {
    mainCols.forEach((col, ci) => {
      merges.push({ s: { r: start, c: ci }, e: { r: start + count - 1, c: ci } });
    });
  });

  // 采购合同块合并
  flatRows.forEach((row, idx) => {
    if (row.__pcIsFirst && row.__pcRows && row.__pcRows > 1) {
      purchaseCols.forEach((col, ci) => {
        const colIndex = header1.indexOf(col); // 通过 header 找到列索引
        if (colIndex >= 0) {
          merges.push({ s: { r: 2 + idx, c: colIndex }, e: { r: 2 + idx + row.__pcRows - 1, c: colIndex } });
        }
      });
    }
  });

  ws['!merges'] = merges;

  // 5️⃣ 写入 Excel
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, '报表');
  XLSX.writeFile(wb, filename);
}
